<!DOCTYPE html>
<html lang="en">

<!-- Head tag (contains Google-Analytics、Baidu-Tongji)-->
<head>
  <!-- Google Analytics -->
  

  <!-- Baidu Tongji -->
  
    <script type="text/javascript">
      // Originial
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?cdf616a127124dd7ff798963125b304d";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  <!-- Baidu Push -->
  
    <script>
      (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
          bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
          bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
      })();
    </script>
  

  <meta charset="utf-8"/>
  <meta http-equiv="X-UA-Compatible" content="IE=edge"/>

  <meta name="google-site-verification" content="lxDfCplOZbIzjhG34NuQBgu2gdyRlAtMB4utP5AgEBc"/>
  <meta name="baidu-site-verification" content="PpzM9WxOJU"/>

  <meta name="viewport" content="width=device-width, initial-scale=1"/>
  <meta name="description" content="It&#39;s an IT blog..."/>
  <meta name="keyword" content="zhuang,IT  blog,Blog"/>
  <link rel="shortcut icon" href="/img/avatar/java-head.png"/>

  <!-- Place this tag in your head or just before your close body tag. -->
  <script async="async" defer="defer" src="https://buttons.github.io/buttons.js"></script>

  
    <!-- Bootstrap Core CSS -->
    <link rel="stylesheet" href="/css/bootstrap.min.css"/>

    <!-- Custom CSS -->
    <link rel="stylesheet" href="/css/beantech.min.css"/>

    <!-- Pygments Highlight CSS -->
    <link rel="stylesheet" href="/css/highlight.css"/>
    <link rel="stylesheet" href="/css/widget.css"/>
    <link rel="stylesheet" href="/css/rocket.css"/>
    <link rel="stylesheet" href="/css/signature.css"/>
    <link rel="stylesheet" href="/css/catalog.css"/>
    <link rel="stylesheet" href="/css/livemylife.css"/>

    
      <!-- wave start -->
      <link rel="stylesheet" href="/css/wave.css"/>
      <!-- wave end -->
    

    
      <!-- top start (article top hot config) -->
      <link rel="stylesheet" href="/css/top.css"/>
      <!-- top end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/scroll.css"/>
      <!-- ThemeColor end -->
    

    
      <!-- viewer start (Picture preview) -->
      <link rel="stylesheet" href="/css/viewer.min.css"/>
      <!-- viewer end -->
    

    
      <!-- Search start -->
      <link rel="stylesheet" href="/css/search.css"/>
      <!-- Search end -->
    

    
      <!-- ThemeColor start -->
      <link rel="stylesheet" href="/css/themecolor.css"/>
      <!-- ThemeColor end -->
    

    

    
  

  <!-- Custom Fonts -->
  <!-- <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet" type="text/css"> -->
  <!-- Hux change font-awesome CDN to qiniu -->
  <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/4.5.0/css/font-awesome.min.css" type="text/css">
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

  <!-- Hux Delete, sad but pending in China <link href='http://fonts.googleapis.com/css?family=Lora:400,700,400italic,700italic' rel='stylesheet' type='text/css'> <link
  href='http://fonts.googleapis.com/css?family=Open+Sans:300italic,400italic,600italic,700italic,800italic,400,300,600,700,800' rel='stylesheet' type='text/ css'> -->

  <!-- HTML5 Shim and Respond.js IE8 support of HTML5 elements and media queries -->
  <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
  <!--[if lt IE 9]> <script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script> <script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script> <![endif]-->

  <!-- ga & ba script hoook -->
  <link rel="canonical" href="http://yoursite-url/cn/mysql/MySQL基础（一）/">
  <title>
    
      MySQL基础（一） - either thin | or die
    
  </title>
<meta name="generator" content="Hexo 5.4.0"></head>


<!-- hack iOS CSS :active style -->

	<body ontouchstart="" class="body--light body--dark">


		<!-- ThemeColor -->
		
		<!-- ThemeColor -->
<style type="text/css">
  .body--light {
    --light-mode: none;
    --dark-mode: block;
  }
  .body--dark {
    --light-mode: block;
    --dark-mode: none;
  }
  i.mdui-icon.material-icons.light-mode {
    display: var(--light-mode);
  }
  i.mdui-icon.material-icons.dark-mode {
    display: var(--dark-mode);
  }
</style>
<div class="toggle" onclick="document.body.classList.toggle('body--dark')">
  <i class="mdui-icon material-icons light-mode"></i>
  <i class="mdui-icon material-icons dark-mode"></i>
</div>
<script>
  //getCookieValue
  function getCookieValue(a) {
    var b = document.cookie.match('(^|[^;]+)\\s*' + a + '\\s*=\\s*([^;]+)');
    return b
      ? b.pop()
      : '';
  }
  let themeMode = 'dark';
  if (getCookieValue('sb-color-mode') && (getCookieValue('sb-color-mode') !== themeMode)) {
    let dbody = document.body.classList;
    themeMode === 'dark' ? dbody.remove('body--dark') : dbody.add('body--dark');
  }

  //setCookieValue
  var toggleBtn = document.querySelector(".toggle");
  toggleBtn.addEventListener("click", function () {
    var e = document.body.classList.contains("body--dark");
    var cookieString = e
      ? "dark"
      : "light";
    var exp = new Date();
    exp.setTime(exp.getTime() + 3 * 24 * 60 * 60 * 1000); //3天过期
    document.cookie = "sb-color-mode=" + cookieString + ";expires=" + exp.toGMTString() + ";path=/";
  });
</script>

		

		<!-- Gitter -->
		

		<!-- Navigation (contains search)-->
		<!-- Navigation -->
<nav class="navbar navbar-default navbar-custom navbar-fixed-top">
  <div class="container-fluid">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header page-scroll">
      <button type="button" class="navbar-toggle">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="/">平平淡淡</a>
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <!-- Known Issue, found by Hux: <nav>'s height woule be hold on by its content. so, when navbar scale out, the <nav> will cover tags. also mask any touch event of tags, unfortunately. -->
    <div id="huxblog_navbar">
      <div class="navbar-collapse">
        <ul class="nav navbar-nav navbar-right">
          <li>
            <a href="/">首页</a>
          </li>

          
          
          
          
          <li>
            <a href="/archive/">
              
              归档
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/tags/">
              
              标签
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/categories/">
              
              分类
              
              
            </a>
          </li>
          
          
          
          <li>
            <a href="/about/">
              
              关于
              
              
            </a>
          </li>
          
          

          
          <li>
            <a class="popup-trigger">
              <span class="search-icon"></span>搜索</a>
          </li>
          

          <!-- LangSelect -->
          
        </ul>
      </div>
    </div>
    <!-- /.navbar-collapse -->
  </div>
  <!-- /.container -->
</nav>
<!-- progress -->
<div id="progress">
  <div class="line" style="width: 0%;"></div>
</div>

<script>
  // Drop Bootstarp low-performance Navbar Use customize navbar with high-quality material design animation in high-perf jank-free CSS3 implementation
  var $body = document.body;
  var $toggle = document.querySelector('.navbar-toggle');
  var $navbar = document.querySelector('#huxblog_navbar');
  var $collapse = document.querySelector('.navbar-collapse');

  $toggle.addEventListener('click', handleMagic)

  function handleMagic(e) {
    if ($navbar.className.indexOf('in') > 0) {
      // CLOSE
      $navbar.className = " ";
      // wait until animation end.
      setTimeout(function() {
        // prevent frequently toggle
        if ($navbar.className.indexOf('in') < 0) {
          $collapse.style.height = "0px"
        }
      }, 400)
    } else {
      // OPEN
      $collapse.style.height = "auto"
      $navbar.className += " in";
    }
  }
</script>


		<!-- Post Header (contains intro-header、signature、wordcount、busuanzi、waveoverlay) -->
		<!-- Modified by Yu-Hsuan Yen -->
<!-- Post Header -->

  <style type="text/css">
    .body--light {
      /* intro-header */
      --intro-header-background-image-url-home: url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: url('//img/header_img/lml_bg.jpg');
    }
    .body--dark {
      --intro-header-background-image-url-home: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/home_bg1.jpg');
      --intro-header-background-image-url-post: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('/img/header_img/lml_bg.jpg');
      --intro-header-background-image-url-page: linear-gradient(rgba(0, 0, 0, 0.1), rgba(0, 0, 0, 0.2)), url('//img/header_img/lml_bg.jpg');
    }

    header.intro-header {
       /*post*/
        background-image: var(--intro-header-background-image-url-post);
        /* background-image: url('/img/header_img/lml_bg.jpg'); */
      
    }

    
      #signature {/*signature*/
        background-image: url('/img/signature/signature.png');
      }
    
  </style>





<header class="intro-header">
  <!-- Signature -->
  <div id="signature">
    <div class="container">
      <div class="row">
        <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
          
          <div class="post-heading">
            <div class="tags">
              
              <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
              
              <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
              
              <a class="tag" href="/tags/#SQL" title="SQL">SQL</a>
              
            </div>
            <h1>MySQL基础（一）</h1>
            <h2 class="subheading"></h2>
            <span class="meta">
              Posted by zhuang on
              2021-10-12
            </span>


            
            <!-- WordCount start -->
            <div class="blank_box"></div>
            <span class="meta">
              Estimated Reading Time <span class="post-count">17</span> Minutes
            </span>
            <div class="blank_box"></div>
            <span class="meta">
              Words <span class="post-count">4k</span> In Total
            </span>
            <div class="blank_box"></div>
            <!-- WordCount end -->
            
            
            <!-- 不蒜子统计 start -->
            <span class="meta" id="busuanzi_container_page_pv">
              Viewed <span id="busuanzi_value_page_pv"><i class="fa fa-spinner fa-spin"></i></span> Times
            </span>
            <!-- 不蒜子统计 end -->
            


          </div>
          
        </div>
      </div>
    </div>
  </div>

  
  <!-- waveoverlay start -->
  <div class="preview-overlay">
    <svg class="preview-waves" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 24 150 28" preserveAspectRatio="none" shape-rendering="auto">
      <defs>
        <path id="gentle-wave" d="M-160 44c30 0 58-18 88-18s 58 18 88 18 58-18 88-18 58 18 88 18 v44h-352z"></path>
      </defs>
      <g class="preview-parallax">
        <use xlink:href="#gentle-wave" x="48" y="0" fill=var(--gentle-wave1)></use>
        <use xlink:href="#gentle-wave" x="48" y="3" fill=var(--gentle-wave2)></use>
        <use xlink:href="#gentle-wave" x="48" y="5" fill=var(--gentle-wave3)></use>
        <use xlink:href="#gentle-wave" x="48" y="7" fill=var(--gentle-wave)></use>
      </g>
    </svg>
  </div>
  <!-- waveoverlay end -->
  

</header>



		<!-- Main Content (Post contains
	Pager、
	tip、
	socialshare、
	gitalk、gitment、disqus-comment、
	Catalog、
	Sidebar、
	Featured-Tags、
	Friends Blog、
	anchorjs、
	) -->
		<!-- Modify by Yu-Hsuan Yen -->
<!-- Post Content -->
<article>
  <div class="container">
    <div class="row">
      <!-- Post Container -->
      <div class="col-lg-8 col-lg-offset-1 col-md-10 col-md-offset-1 post-container">

        <h2 id="SELECT"><a href="#SELECT" class="headerlink" title="SELECT"></a>SELECT</h2><p>SELECT语句从表或视图中获取数据，SELECT语句的结果称为结果集，是一个行列表，每个行包含相同数量的列。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	column_1, column_2, ...</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_1</span><br><span class="line">[<span class="keyword">INNER</span> <span class="operator">|</span> <span class="keyword">LEFT</span> <span class="operator">|</span> <span class="keyword">RIGHT</span>] <span class="keyword">JOIN</span> table_2 <span class="keyword">ON</span> conditions</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	conditions</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> column_1</span><br><span class="line"><span class="keyword">HAVING</span> group_coditions</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> column_1</span><br><span class="line">LIMIT <span class="keyword">offset</span>, length;</span><br></pre></td></tr></table></figure>
<ul>
<li>SELECT ：后跟逗号分隔列或星号（*）列表，表示返回所有列。</li>
<li>FROM ：指定要查询数据的表或视图</li>
<li>JOIN：根据特定的连接条件从其他表中获取相关数据</li>
<li>WHERE：子句过滤结果集中的行</li>
<li>GROUP BY ：子句将一组行分组到组中，并在每个组上应用聚合函数</li>
<li>HAVING：子句根据GROUP BY子句定义的组过滤组</li>
<li>ORDER BY：子句指定用于排序的列的列表</li>
<li>LIMIT：约束返回的行数</li>
</ul>
<h2 id="DISTINCT"><a href="#DISTINCT" class="headerlink" title="DISTINCT"></a>DISTINCT</h2><p>查询数据时，可能会出现重复的行，在SELECT子句中使用<code>DISTINCT</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span></span><br><span class="line">	columns</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_name</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	where_conditions;</span><br></pre></td></tr></table></figure>
<h3 id="DISTINCT和NULL值"><a href="#DISTINCT和NULL值" class="headerlink" title="DISTINCT和NULL值"></a>DISTINCT和NULL值</h3><p>如果字段中有<code>NULL</code>值并且要对该列使用<code>DISTINCT</code>子句，则保留一个<code>NULL</code>值。</p>
<h3 id="DISTINCT多列"><a href="#DISTINCT多列" class="headerlink" title="DISTINCT多列"></a>DISTINCT多列</h3><p><code>DISTINCT</code>子句和多个列一起使用，使用这些列中的值组合来确定结果集中行的唯一性。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> </span><br><span class="line">	state,</span><br><span class="line">	city</span><br><span class="line"><span class="keyword">from</span> 	</span><br><span class="line">	customers c</span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	c.state <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> state, city; </span><br></pre></td></tr></table></figure>
<h3 id="DISTINCT语句与GROUP-BY语句"><a href="#DISTINCT语句与GROUP-BY语句" class="headerlink" title="DISTINCT语句与GROUP BY语句"></a>DISTINCT语句与GROUP BY语句</h3><p>在不使用聚合函数的情况下在<code>SELECT</code>语句中使用<code>GROUP BY</code>子句，则<code>GROUP BY</code>子句的行为与<code>DISTINCT</code>子句类似.</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> </span><br><span class="line">	state</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.state </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	c.state;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> </span><br><span class="line">	state</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line">	c.state;</span><br></pre></td></tr></table></figure>
<h3 id="DISTINCT和聚合函数"><a href="#DISTINCT和聚合函数" class="headerlink" title="DISTINCT和聚合函数"></a>DISTINCT和聚合函数</h3><p><code>DISTINCT</code>子句与聚合函数一起使用，将聚合函数应用结果集之前删除重复行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 美国客户的不同的州数 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	<span class="built_in">count</span>(<span class="keyword">distinct</span> state)</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	c.country  <span class="operator">=</span> <span class="string">&#x27;USA&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h3 id="DISTINCT和LIMIT子句"><a href="#DISTINCT和LIMIT子句" class="headerlink" title="DISTINCT和LIMIT子句"></a>DISTINCT和LIMIT子句</h3><p>会找到<code>LIMIT</code>子句中指定的唯一行数时立即停止搜索。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查询选择前五个非null唯一州 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.state </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	c.state <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span> limit <span class="number">5</span>;</span><br></pre></td></tr></table></figure>
<h2 id="ORDER-BY"><a href="#ORDER-BY" class="headerlink" title="ORDER BY"></a>ORDER BY</h2><p>对结果集进行排序，单列或多列对结果集进行排序，按升序或降序对不同列的结果集进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column1, column2, ...</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> column1 [<span class="keyword">ASC</span> <span class="operator">|</span> <span class="keyword">DESC</span>], column2 [<span class="keyword">ASC</span> <span class="operator">|</span> <span class="keyword">DESC</span>], ...</span><br></pre></td></tr></table></figure>
<p><code>ASC</code>代表升序和<code>DESC</code>代表降序，默认按升序结果集进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 按姓氏降序排序联系人，按升序排序第一个名称 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	c.contactLastName ,</span><br><span class="line">	c.contactFirstName </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span></span><br><span class="line">	c.contactLastName <span class="keyword">desc</span>,</span><br><span class="line">	c.contactFirstName <span class="keyword">asc</span>;</span><br></pre></td></tr></table></figure>
<p>order by还可以按表达式进行排序，使用<code>FIELD()</code>函数为列中的值定义自己的自定义排序顺序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	orderNumber, status</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	orders</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> FIELD(status,</span><br><span class="line">	<span class="string">&#x27;In Process&#x27;</span>,</span><br><span class="line">	<span class="string">&#x27;On Hold&#x27;</span>,</span><br><span class="line">	<span class="string">&#x27;Cancelled&#x27;</span>,</span><br><span class="line">	<span class="string">&#x27;Resolved&#x27;</span>,</span><br><span class="line">	<span class="string">&#x27;Disputed&#x27;</span>,<span class="string">&#x27;Shipped&#x27;</span>); </span><br></pre></td></tr></table></figure>
<h2 id="WHERE"><a href="#WHERE" class="headerlink" title="WHERE"></a>WHERE</h2><p><code>WHERE</code>子句允许指定查询返回的行的搜索条件。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	select_list</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_name</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	search_condition;</span><br></pre></td></tr></table></figure>
<p>除了<code>SELECT</code>语句之外，还可在<code>WHERE</code>z子句使用and指定要更新和删除的行.<code>UPDATE DELETE</code>。</p>
<p><code>WHERE</code>子句中形成过滤表达式的比较运算符</p>
<div class="table-container">
<table>
<thead>
<tr>
<th>运算符</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>=</td>
<td>等于</td>
</tr>
<tr>
<td>&lt;&gt;或!=</td>
<td>不等于</td>
</tr>
<tr>
<td>&lt;</td>
<td>小于</td>
</tr>
<tr>
<td>&gt;</td>
<td>大于</td>
</tr>
<tr>
<td>&lt;=</td>
<td>小于或等于</td>
</tr>
<tr>
<td>&gt;=</td>
<td>大于或等于</td>
</tr>
</tbody>
</table>
</div>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查找不是销售代表的所有员工 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	concat(e.lastName, &quot; &quot;, e.firstName) <span class="keyword">as</span> name ,</span><br><span class="line">	e.jobTitle </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	employees e </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	e.jobTitle <span class="operator">&lt;&gt;</span> <span class="string">&#x27;Sales Rep&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h2 id="AND"><a href="#AND" class="headerlink" title="AND"></a>AND</h2><p>当两个表达式为true返回true。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">boolean_expression_1 <span class="keyword">AND</span> boolean_expression_2</span><br></pre></td></tr></table></figure>
<p>逻辑操作符<code>AND</code>和<code>OR</code>称为短路操作符：参数从左向右解析，一旦结果可以确定旧停止</p>
<h2 id="OR"><a href="#OR" class="headerlink" title="OR"></a>OR</h2><p>当任一条件为真时返回true</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">boolean_expression_1 <span class="keyword">OR</span> boolean_expression_2</span><br></pre></td></tr></table></figure>
<p>运算符优先级，<code>AND</code>运算符之后计算<code>OR</code>运算符</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="literal">true</span> <span class="keyword">OR</span> <span class="literal">false</span> <span class="keyword">AND</span> <span class="literal">false</span>;</span><br></pre></td></tr></table></figure>
<ul>
<li>首先AND运算符，表达式false AND false 返回false</li>
<li>其次OR运算符，表达式true OR false 返回true</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 美国或法国且信用额度大于10,000的客户 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerName ,</span><br><span class="line">	c.country ,</span><br><span class="line">	c.creditLimit </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	(c.country <span class="operator">=</span> <span class="string">&#x27;USA&#x27;</span> <span class="keyword">or</span> c.country <span class="operator">=</span> <span class="string">&#x27;France&#x27;</span>)</span><br><span class="line">	<span class="keyword">and</span> c.creditLimit  <span class="operator">&gt;</span> <span class="number">10000</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">/* 位于美国的客户或位于法国且信用额度大于10,000的客户 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerName ,</span><br><span class="line">	c.country ,</span><br><span class="line">	c.creditLimit </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	customers c </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	c.country <span class="operator">=</span> <span class="string">&#x27;USA&#x27;</span> <span class="keyword">or</span> c.country <span class="operator">=</span> <span class="string">&#x27;France&#x27;</span></span><br><span class="line">	<span class="keyword">and</span> c.creditLimit  <span class="operator">&gt;</span> <span class="number">10000</span>;</span><br></pre></td></tr></table></figure>
<h2 id="IN"><a href="#IN" class="headerlink" title="IN"></a>IN</h2><p>允许你确定一个指定的值在一组值匹配的任何值和子查询。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	column1, column2, ...</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_name</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	(expr <span class="operator">|</span> column_1) <span class="keyword">IN</span> (<span class="string">&#x27;value1&#x27;</span>, <span class="string">&#x27;value2&#x27;</span>, ...);</span><br></pre></td></tr></table></figure>
<p><code>IN</code>与<code>NOT</code>组合检查是否与列表或子查询中的任何值不匹配。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查找位于美国和法国的办事处 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	o.officeCode ,</span><br><span class="line">	o.city ,</span><br><span class="line">	o.phone ,</span><br><span class="line">	o.country </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	offices o </span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	o.country <span class="keyword">in</span> (<span class="string">&#x27;USA&#x27;</span>, <span class="string">&#x27;France&#x27;</span>);</span><br></pre></td></tr></table></figure>
<p>IN与子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 订单的总价值是大于60,000 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	o.orderNumber ,</span><br><span class="line">	o.status </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	orders o </span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	o.orderNumber <span class="keyword">in</span> (</span><br><span class="line">		<span class="keyword">select</span> o2.orderNumber </span><br><span class="line">		<span class="keyword">from</span> orderdetails o2 </span><br><span class="line">		<span class="keyword">group</span> <span class="keyword">by</span> o2.orderNumber </span><br><span class="line">		<span class="keyword">having</span> <span class="built_in">sum</span>(o2.quantityOrdered <span class="operator">*</span> o2.priceEach) <span class="operator">&gt;</span> <span class="number">60000</span></span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<h2 id="BETWEEN"><a href="#BETWEEN" class="headerlink" title="BETWEEN"></a>BETWEEN</h2><p>指定是否在某个范围内的值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">expr [<span class="keyword">NOT</span>] <span class="keyword">BETWEEN</span> begin_expr <span class="keyword">AND</span> end_expr;</span><br></pre></td></tr></table></figure>
<p><code>expr</code>是<code>begin_expr</code>和<code>end_expr</code>定义的范围内测试的表达式，必须具有相同的数据类型。</p>
<p>返回的值为真，expr大于或等于(&gt;=)的值<code>begin_expr</code>，且小于或等于（&lt;=）的值<code>end_expr</code>,否则返回零。</p>
<p>如有任何表达式NULL，则BETWEEN返回NULL</p>
<p>如指定独占范围，可以使用大于&gt;或小于&lt;运算符</p>
<p>使用<code>BETWEEN</code>带有日期的运算符时，应使用类型转换将列或表达式的类型显式转换为DARE类型</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 01/01/2003到01/31/2003之间具有所需日期的订单 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	o.orderNumber,</span><br><span class="line">	o.requiredDate,</span><br><span class="line">	o.status </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	orders o </span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	o.requiredDate <span class="keyword">between</span> </span><br><span class="line">		<span class="built_in">cast</span>(<span class="string">&#x27;2013-01-01&#x27;</span> <span class="keyword">as</span> <span class="type">date</span>) <span class="keyword">and</span> </span><br><span class="line">	    <span class="built_in">cast</span>(<span class="string">&#x27;2013-01-31&#x27;</span> <span class="keyword">as</span> <span class="type">date</span>);</span><br></pre></td></tr></table></figure>
<h2 id="LIKE"><a href="#LIKE" class="headerlink" title="LIKE"></a>LIKE</h2><p><code>LIKE</code>运算符检测一个字符串是否包含指定的或不包含, LIKE用于<code>SELECT</code>、<code>UPDATE</code>、<code>DELETE</code>语句的<code>WHERE</code>子句中。</p>
<p>MySQL提供两个通配符使用：</p>
<ul>
<li>百分号(<code>%</code>)匹配任何零个或多个字符的字符串</li>
<li>下划线(<code>_</code>)通配符匹配任何单个字符。</li>
</ul>
<p><code>NOT</code>和<code>LIKE</code>组合查找与特定模式不匹配的字符串</p>
<p>LIKE与ESCAPE子句，使用<code>ESCAPE</code>子句指定转义字符，如未明确指定转义字符，则反斜杠<code>\</code>是缺省转义字符。</p>
<h2 id="LIMIT"><a href="#LIMIT" class="headerlink" title="LIMIT"></a>LIMIT</h2><p><code>SELECT</code>语句中使用<code>LIMIT</code>子句来约束结果集中的行数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	column1, column2, ...</span><br><span class="line"><span class="keyword">FROM</span> </span><br><span class="line">	<span class="keyword">table</span></span><br><span class="line">LIMIT <span class="keyword">offset</span>, count;</span><br></pre></td></tr></table></figure>
<ul>
<li>offset第一行的偏移规定返回，第一行是0，而不是1</li>
<li>count指定要返回行的最大数目</li>
</ul>
<p><img src="image-20211018084453649.png" alt="image-20211018084453649"></p>
<p>当使用带有一个参数的LIMIT子句时，将用于确定从结果集的开头返回的最大行数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	select_list</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_name</span><br><span class="line">LIMIT count;</span><br></pre></td></tr></table></figure>
<h3 id="获取前N行"><a href="#获取前N行" class="headerlink" title="获取前N行"></a>获取前N行</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查询前10个客户 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerNumber ,</span><br><span class="line">	c.customerName ,</span><br><span class="line">	c.creditLimit </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line">limit <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h3 id="获取最高和最低值"><a href="#获取最高和最低值" class="headerlink" title="获取最高和最低值"></a>获取最高和最低值</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 获取最高和最低值 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerNumber ,</span><br><span class="line">	c.customerName ,</span><br><span class="line">	c.creditLimit </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> c.creditLimit <span class="keyword">desc</span></span><br><span class="line">limit <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerNumber ,</span><br><span class="line">	c.customerName ,</span><br><span class="line">	c.creditLimit </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> c.creditLimit <span class="keyword">asc</span> </span><br><span class="line">limit <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<h3 id="获取第N个最高值"><a href="#获取第N个最高值" class="headerlink" title="获取第N个最高值"></a>获取第N个最高值</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	select_List</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	<span class="keyword">table</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">	sort_expression <span class="keyword">DESC</span></span><br><span class="line">LIMIT nth <span class="operator">-</span> <span class="number">1</span>, count;s</span><br></pre></td></tr></table></figure>
<h2 id="IS-NULL"><a href="#IS-NULL" class="headerlink" title="IS NULL"></a>IS NULL</h2><p>测试值是否值<code>NULL</code>，使用<code>IS NULL</code>运算符，值为NULL，则返回true，否则返回false</p>
<p>检查值是否不是<code>NULL</code>，使用<code>IS NOT NULL</code>运算符</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.customerName,</span><br><span class="line">	c.salesRepEmployeeNumber </span><br><span class="line"><span class="keyword">from</span> customers c </span><br><span class="line"><span class="keyword">where</span> c.salesRepEmployeeNumber <span class="keyword">is</span> <span class="keyword">null</span>;</span><br></pre></td></tr></table></figure>
<h2 id="别名"><a href="#别名" class="headerlink" title="别名"></a>别名</h2><p>为列提供别名，使用<code>AS</code>关键字后跟别名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	[column_1 <span class="operator">|</span> expression] <span class="keyword">AS</span> descriptive_name</span><br><span class="line"><span class="keyword">FROM</span> table_name;</span><br></pre></td></tr></table></figure>
<p>在MySQL中，可以使用ORDER BY、GROUP BY和HVING子句中来引用列的别名</p>
<blockquote>
<p>不能再WHERE子句中使用别名，当MySQL评估WHERE子句时，SELECT子句中指定的列的值可能尚未确定</p>
</blockquote>
<p>MySQL表别名，使用<code>AS</code>关键字为表分配别名，经常再INNER JOIN、LEFT JOIN、self JOIN子句和子查询的语句中使用表别名</p>
<h2 id="JOIN"><a href="#JOIN" class="headerlink" title="JOIN"></a>JOIN</h2><p>一种基于表之间的公共列的值在一个（自连接）或更多表之间链接数据的方法。</p>
<p>Mysql支持以下类型的连接：</p>
<ul>
<li>CROSS JOIN</li>
</ul>
<p><img src="image-20211016091952331.png" alt="image-20211016091952331"></p>
<ul>
<li>LEFT JOIN</li>
</ul>
<p><img src="image-20211016092035797.png" alt="image-20211016092035797"></p>
<ul>
<li>RGIHT JOIN</li>
</ul>
<p><img src="image-20211016092048620.png" alt="image-20211016092048620"></p>
<ul>
<li>INNER JOIN</li>
</ul>
<p><img src="image-20211016092023802.png" alt="image-20211016092023802"></p>
<h2 id="INNER-JOIN"><a href="#INNER-JOIN" class="headerlink" title="INNER JOIN"></a>INNER JOIN</h2><p><code>INNER JOIN</code>子句将一个表中的行与其他表中的行进行匹配，并允许查询包含两个表中列的行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_list</span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> t2 <span class="keyword">ON</span> join_condition1;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>对于t1表中的每一行，<code>INNER JOIN</code>子句将其与t2表的每一行进行比较，以检查它们是否都满足连接条件，当满足连接条件时，<code>INNER JOIN</code>将返回一个新行，行有两个表t1和t2表组成。</p>
<p><img src="image-20211016090457107.png" alt="image-20211016090457107"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 获取订单号，订单状态和总销售额 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	o.orderNumber ,</span><br><span class="line">	o.status ,</span><br><span class="line">	<span class="built_in">sum</span>(o2.quantityOrdered <span class="operator">*</span> o2.priceEach) <span class="keyword">as</span> total</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	orders o </span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o2 <span class="keyword">on</span> o2.orderNumber <span class="operator">=</span> o.orderNumber </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	o.orderNumber ;</span><br></pre></td></tr></table></figure>
<p>INNER JOIN使用非等于运算符</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查找代码S10_1678小于产品的制造商建议零售价（MSRP）的产品的销售价格 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	p.productName ,</span><br><span class="line">	o.orderNumber ,</span><br><span class="line">	p.MSRP ,</span><br><span class="line">	o.priceEach </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	products p </span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o <span class="keyword">on</span> o.productCode <span class="operator">=</span> p.productCode </span><br><span class="line">		<span class="keyword">and</span> p.MSRP <span class="operator">&gt;</span> o.priceEach </span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	p.productCode  <span class="operator">=</span> <span class="string">&#x27;S10_1678&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h2 id="LEFT-JOIN"><a href="#LEFT-JOIN" class="headerlink" title="LEFT JOIN"></a>LEFT JOIN</h2><p>left join子句允许查询两个或多个数据库表的数据。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	t1.c1, t1.c2, t2.c1, t2.c2</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	t1</span><br><span class="line">		<span class="keyword">left</span> <span class="keyword">join</span></span><br><span class="line">	t2 <span class="keyword">on</span> t1.c1 <span class="operator">=</span> t2.c1;</span><br></pre></td></tr></table></figure>
<p><img src="image-20211016084213090.png" alt="image-20211016084213090"></p>
<p>两个圆之间的交集是在两个表中匹配的行，而左圆的剩余部分是t1表中没有表中任何匹配的行的行t2，因此，左表的所有行都包含在结果集中。</p>
<blockquote>
<p>如果查询中<code>WHERE</code>和<code>having</code>子句，则返回匹配条件的行</p>
</blockquote>
<h3 id="WHERE子句与ON子句中的条件"><a href="#WHERE子句与ON子句中的条件" class="headerlink" title="WHERE子句与ON子句中的条件"></a>WHERE子句与ON子句中的条件</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	o.orderNumber ,</span><br><span class="line">	o.customerNumber ,</span><br><span class="line">	o2.productCode </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	orders o </span><br><span class="line">		<span class="keyword">left</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o2 <span class="keyword">using</span>(orderNumber)</span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	o.orderNumber  <span class="operator">=</span> <span class="number">10123</span>;</span><br></pre></td></tr></table></figure>
<p><img src="image-20211016092735108.png" alt="image-20211016092735108"></p>
<p>将where子句移动到on子句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	o.orderNumber ,</span><br><span class="line">	o.customerNumber ,</span><br><span class="line">	o2.productCode </span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	orders o </span><br><span class="line">		<span class="keyword">left</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o2 </span><br><span class="line">		<span class="keyword">on</span> o.orderNumber <span class="operator">=</span> o2.orderNumber</span><br><span class="line">		<span class="keyword">and</span> o.orderNumber <span class="operator">=</span> <span class="number">10123</span>;</span><br></pre></td></tr></table></figure>
<p><img src="image-20211016093129052.png" alt="image-20211016093129052"></p>
<p>在这种情况下，查询将返回所有订单，但只有订单10123将具有与之关联的详细信息。</p>
<blockquote>
<p><code>INNER JOIN</code>子句中的<code>ON</code>子句条件等同于<code>WHERE</code>子句中的条件</p>
</blockquote>
<h2 id="RIGHT-JOIN"><a href="#RIGHT-JOIN" class="headerlink" title="RIGHT JOIN"></a>RIGHT JOIN</h2><p><code>RIGHT JOIN</code>类似于<code>LEFT JOIN</code>是对表反转的处理。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> t1</span><br><span class="line"><span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> t2 <span class="keyword">ON</span> join_predicate;</span><br></pre></td></tr></table></figure>
<p>join_predicate写法：</p>
<blockquote>
<p>t1.pk = t2.pk</p>
</blockquote>
<p>或者两个表的公共列具有<strong><em>相同的名称</em></strong>，则使用以下语法：</p>
<blockquote>
<p>USING(common_column);</p>
</blockquote>
<p>工作原理</p>
<ul>
<li>t2表中的所有行（右表）将在结果集中至少出现一次</li>
<li>基于join_predicate条件，如果t1表中没有匹配的行（左表），则t1表中的列中将显示<code>NULL</code></li>
</ul>
<p><img src="image-20211015092559354.png" alt="image-20211015092559354"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 销售代表及其客户*/</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	concat(e.firstName, <span class="string">&#x27; &#x27;</span>, e.lastName) salesman,</span><br><span class="line">	e.jobTitle ,</span><br><span class="line">	c.customerName </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	employees e </span><br><span class="line">		<span class="keyword">right</span> <span class="keyword">join</span></span><br><span class="line">	customers c <span class="keyword">on</span> c.salesRepEmployeeNumber <span class="operator">=</span> e.employeeNumber </span><br><span class="line">		<span class="keyword">and</span> e.jobTitle <span class="operator">=</span> <span class="string">&#x27;Sales Rep&#x27;</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> c.customerName ;</span><br></pre></td></tr></table></figure>
<h2 id="CROSS-JOIN"><a href="#CROSS-JOIN" class="headerlink" title="CROSS JOIN"></a>CROSS JOIN</h2><p>表中行的笛卡尔积。结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行于第二个表中的行的组合。</p>
<blockquote>
<p>如果每个表有1000行，结果集将获得1000*1ooo的行，这是巨大的</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table1</span><br><span class="line">		<span class="keyword">CROSS</span> <span class="keyword">JOIN</span></span><br><span class="line">	table2;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>与<code>INNER JOIN</code> 或<code>LEFT JOIN</code> 子句不同，<code>CROSS JOIN</code>子句没有连接条件</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 每个商店和每个产品的总销售额,包括商品没有的产品，设置为0 */</span>	</span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	s.store_name ,</span><br><span class="line">	p.product_name,</span><br><span class="line">	ifnull(c.revenue, <span class="number">0</span>) <span class="keyword">as</span> <span class="string">&#x27;revenue&#x27;</span></span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	products <span class="keyword">as</span> p</span><br><span class="line">		<span class="keyword">cross</span> <span class="keyword">join</span></span><br><span class="line">	stores <span class="keyword">as</span> s</span><br><span class="line">		<span class="keyword">left</span> <span class="keyword">join</span> (</span><br><span class="line">			<span class="keyword">select</span></span><br><span class="line">				s2.id <span class="keyword">as</span> <span class="string">&#x27;stores_id&#x27;</span>,</span><br><span class="line">				p.id <span class="keyword">as</span> <span class="string">&#x27;product_id&#x27;</span>,</span><br><span class="line">				s2.store_name,</span><br><span class="line">				p.product_name,</span><br><span class="line">				round(<span class="built_in">sum</span>(s.quantity <span class="operator">*</span> p.price), <span class="number">0</span>) <span class="keyword">as</span> <span class="string">&#x27;revenue&#x27;</span></span><br><span class="line">			<span class="keyword">from</span> </span><br><span class="line">				sales s </span><br><span class="line">					<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">				products p <span class="keyword">on</span> p.id <span class="operator">=</span> s.product_id </span><br><span class="line">					<span class="keyword">inner</span> <span class="keyword">join</span> </span><br><span class="line">				stores s2 <span class="keyword">on</span> s2.id <span class="operator">=</span> s.store_id </span><br><span class="line">			<span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">				s2.store_name, p.product_name, s2.id, p.id</span><br><span class="line">			) <span class="keyword">as</span> c </span><br><span class="line">			<span class="keyword">on</span> c.stores_id <span class="operator">=</span> s.id <span class="keyword">and</span> c.product_id <span class="operator">=</span> p.id</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> s.store_name ;</span><br></pre></td></tr></table></figure>
<blockquote>
<p>如果收入为NULL（商品没有销售），则查询使用<code>IFNULL</code>函数返回0</p>
</blockquote>
<p>使用<code>CROSS JOIN</code>查询，可以回答类似，即时销售人员在特定月份没有销售，也可以按月查找销售员的销售收入。</p>
<h2 id="SELF-JOIN"><a href="#SELF-JOIN" class="headerlink" title="SELF JOIN"></a>SELF JOIN</h2><p>self join自联接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 显示位于同一城市的客户列表 */</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	c.city ,</span><br><span class="line">	c.customerName,</span><br><span class="line">	c2.customerName </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers c </span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	customers c2 <span class="keyword">on</span> c2.city <span class="operator">=</span> c.city </span><br><span class="line">		<span class="keyword">and</span> c.customerName  <span class="operator">&gt;</span> c2.customerName </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> c.city </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>允许<code>INNER JOIN</code>或<code>LEFY JOIN</code>子句将表连接到自身。</p>
<h2 id="GROUP-BY"><a href="#GROUP-BY" class="headerlink" title="GROUP BY"></a>GROUP BY</h2><p><code>GROUP BY</code>子句按行或表达式的值将一组行分组为一组摘要行，为每个组返回一行，减少结果集中的行数，经常于聚合函数使用</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	c1, c2, ..., cn, aggregate_function(ci)</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table_name</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	where_conditions</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">	c1, c2, ..., cn;</span><br></pre></td></tr></table></figure>
<p><code>GROUP BY</code>必须出现在<code>FROM</code>和<code>WHERE</code>之后。</p>
<h3 id="简单示例"><a href="#简单示例" class="headerlink" title="简单示例"></a>简单示例</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 订单状态 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	status,</span><br><span class="line">	<span class="built_in">count</span>(<span class="operator">*</span>)</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	orders o </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	status;</span><br></pre></td></tr></table></figure>
<pre><code>### 聚合函数
</code></pre><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 按状态得到的所有订单的总金额 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	status,</span><br><span class="line">	<span class="built_in">sum</span>(o.quantityOrdered <span class="operator">*</span> o.priceEach) <span class="keyword">as</span> total</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	orders</span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o <span class="keyword">using</span>(orderNumber)</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	status ;</span><br></pre></td></tr></table></figure>
<h3 id="表达式"><a href="#表达式" class="headerlink" title="表达式"></a>表达式</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查询获取每年的总销售额 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	<span class="keyword">year</span>(o.orderDate),</span><br><span class="line">	<span class="built_in">sum</span>(o2.quantityOrdered <span class="operator">*</span> o2.priceEach) <span class="keyword">as</span> total</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	orders o </span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o2 <span class="keyword">using</span>(orderNumber)</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	o.status  <span class="operator">=</span> <span class="string">&#x27;Shipped&#x27;</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	<span class="keyword">year</span>(o.orderDate);</span><br></pre></td></tr></table></figure>
<h3 id="GROUP-BY-与HAVING子句实例"><a href="#GROUP-BY-与HAVING子句实例" class="headerlink" title="GROUP BY 与HAVING子句实例"></a>GROUP BY 与HAVING子句实例</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 2003年后的年份总销售额 */</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">	<span class="keyword">year</span>(o.orderDate) <span class="keyword">as</span> <span class="keyword">year</span>,</span><br><span class="line">	<span class="built_in">sum</span>(o2.quantityOrdered <span class="operator">*</span> o2.priceEach) <span class="keyword">as</span> total</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	orders o </span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orderdetails o2 <span class="keyword">using</span>(orderNumber)</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	o.status  <span class="operator">=</span> <span class="string">&#x27;Shipped&#x27;</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	<span class="keyword">year</span></span><br><span class="line"><span class="keyword">having</span></span><br><span class="line">	<span class="keyword">year</span> <span class="operator">&gt;</span> <span class="number">2013</span>;</span><br></pre></td></tr></table></figure>
<h3 id="MySQL与标准SQL"><a href="#MySQL与标准SQL" class="headerlink" title="MySQL与标准SQL"></a>MySQL与标准SQL</h3><blockquote>
<p>标准SQL不允许在<code>group by</code>子句中使用别名，但MySQL支持此功能。</p>
<p>MySQL还允许按升序或降序对组进行排序，标准SQL不然，默认为升序。</p>
</blockquote>
<h2 id="HAVING"><a href="#HAVING" class="headerlink" title="HAVING"></a>HAVING</h2><p><code>HAVING</code>子句来指定一组行或聚合的过滤条件。通常与<code>GROUP BY</code>一起使用。</p>
<blockquote>
<p><code>HAVING</code>子句将过滤条件应用于每组行，而<code>WHERE</code>子句将过滤条件应用于每个单独的行</p>
</blockquote>
<p>只有将<code>GROUP BY</code>子句于<code>HAVING</code>子句一起使用，子句才有用，例如：统计问题，查找本月、本季度或本年总销售额的订单数量</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 每个订单销售的商品数量以及每个订单的总销售额</span></span><br><span class="line"><span class="comment"> * 查找已发货且总销售额大于1500的所有订单</span></span><br><span class="line"><span class="comment"> */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	orderNumber,</span><br><span class="line">	status,</span><br><span class="line">	<span class="built_in">sum</span>(quantityOrdered) <span class="keyword">as</span> itemsCount,</span><br><span class="line">	<span class="built_in">sum</span>(quantityOrdered <span class="operator">*</span> priceEach) <span class="keyword">as</span> total</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	orderdetails</span><br><span class="line">		<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">	orders <span class="keyword">using</span>(orderNumber)</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	orderNumber,</span><br><span class="line">	status</span><br><span class="line"><span class="keyword">having</span> </span><br><span class="line">	total <span class="operator">&gt;</span> <span class="number">1500</span> <span class="keyword">and</span> status <span class="operator">=</span> <span class="string">&#x27;Shipped&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h2 id="ROLLUP"><a href="#ROLLUP" class="headerlink" title="ROLLUP"></a>ROLLUP</h2><p>ROLLUP子句是GROUP BY 子句的扩展</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	select_list</span><br><span class="line"><span class="keyword">FROM</span> </span><br><span class="line">	table_name</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span></span><br><span class="line">	c1, c2, c3</span><br><span class="line"><span class="keyword">WITH</span> <span class="keyword">ROLLUP</span>;</span><br></pre></td></tr></table></figure>
<p>如果在GROUP BY子句中指定了多个列，则ROLLUP子句输入列之间存在优先级结构。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> c1, c2, c3 <span class="keyword">WITH</span> <span class="keyword">ROLLUP</span>;</span><br></pre></td></tr></table></figure>
<p>有以下优先级</p>
<p>c1 &gt; c2 &gt; c3</p>
<p>会生成以下分组集</p>
<blockquote>
<p>(c1, c2, c3)</p>
<p>(c1, c2)</p>
<p>(c1)</p>
<p>()</p>
</blockquote>
<h3 id="GROUPING函数"><a href="#GROUPING函数" class="headerlink" title="GROUPING函数"></a>GROUPING函数</h3><p>检查NULL结果集中是否表示小计或总计，使用<code>GROUPING()</code>函数</p>
<p>当NULL在超级聚合是，<code>GROUPING()</code>函数返回1，否则返回0</p>
<p><code>GROUPING()</code>函数用于查询、HAVING子句和ORDER BY 子句</p>
<p>将有意义的标签替换超级聚合NULL值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	if (<span class="keyword">grouping</span>(productLine), <span class="string">&#x27;all Product Lines&#x27;</span>, productLine) <span class="keyword">as</span> <span class="string">&#x27;productLine&#x27;</span>,</span><br><span class="line">	if (<span class="keyword">grouping</span>(orderYear), <span class="string">&#x27;all years&#x27;</span>, orderYear) <span class="keyword">as</span> <span class="string">&#x27;orderYear&#x27;</span>,</span><br><span class="line">	<span class="built_in">sum</span>(orderValue) totalOrderValue</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	sales</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">	productLine,</span><br><span class="line">	orderYear </span><br><span class="line"><span class="keyword">with</span> <span class="keyword">rollup</span>;</span><br></pre></td></tr></table></figure>
<h2 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h2><p>子查询称为内部查询，包含子查询的查询称为外部查询</p>
<p><img src="image-20211013083802416.png" alt="image-20211013083802416"></p>
<h3 id="子查询带有比较运算符"><a href="#子查询带有比较运算符" class="headerlink" title="子查询带有比较运算符"></a>子查询带有比较运算符</h3><p>使用比较运算符 = &gt;  &lt;等将子查询返回的单个值与WHERE子句中的表达式进行比较</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查询具有最高付款金额的客户 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	customerNumber, checkNumber, amount </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	payments</span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	amount <span class="operator">=</span> (</span><br><span class="line">		<span class="keyword">select</span> <span class="built_in">max</span>(amount)</span><br><span class="line">		<span class="keyword">from</span> payments</span><br><span class="line">	);</span><br><span class="line"><span class="comment">/* 查找付款大于平均付款的客户。 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	customerNumber, checkNumber, amount </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	payments</span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	amount <span class="operator">&gt;</span> (</span><br><span class="line">		<span class="keyword">select</span> <span class="built_in">avg</span>(amount)</span><br><span class="line">		<span class="keyword">from</span> payments</span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<h3 id="子查询带有IN和NOT-IN运算符"><a href="#子查询带有IN和NOT-IN运算符" class="headerlink" title="子查询带有IN和NOT IN运算符"></a>子查询带有IN和NOT IN运算符</h3><p>如果子查询返回多个值，则可以在WHERE子句中使用IN或NOT IN 运算符</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查找没有订单的客户 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	customerName </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	customerNumber <span class="keyword">not</span> <span class="keyword">in</span> (</span><br><span class="line">		<span class="keyword">select</span> <span class="keyword">distinct</span></span><br><span class="line">			customerNumber</span><br><span class="line">		<span class="keyword">from</span> </span><br><span class="line">			orders</span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<p>在FROM子句中使用子查询，从子查询返回的结果集将用作临时表，此表为派生表或实现子查询</p>
<h3 id="相关子查询"><a href="#相关子查询" class="headerlink" title="相关子查询"></a>相关子查询</h3><p>独立子查询作为独立查询执行，相关子查询是使用外部查询中的数据的子查询。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 购买价格高于每个 产品系列中所有产品的平均购买价格的产品 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	productName, buyPrice </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	products p </span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	buyPrice <span class="operator">&gt;</span> (</span><br><span class="line">		<span class="keyword">select</span> </span><br><span class="line">			<span class="built_in">avg</span>(buyPrice)</span><br><span class="line">		<span class="keyword">from</span> </span><br><span class="line">			products p2 </span><br><span class="line">		<span class="keyword">where</span></span><br><span class="line">			p.productLine <span class="operator">=</span> p2.productLine </span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<h3 id="子查询带有EXISTS和NOT-EXISTS"><a href="#子查询带有EXISTS和NOT-EXISTS" class="headerlink" title="子查询带有EXISTS和NOT EXISTS"></a>子查询带有EXISTS和NOT EXISTS</h3><p>子查询返回 TRUE或FALSE</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 查询选择总值大于60K的销售订单的顾客 */</span></span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">	customerNumber,</span><br><span class="line">	customerName </span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">	customers</span><br><span class="line"><span class="keyword">where</span> </span><br><span class="line">	<span class="keyword">exists</span> (</span><br><span class="line">		<span class="keyword">select</span> </span><br><span class="line">			orderNumber,</span><br><span class="line">			<span class="built_in">sum</span>(priceEach <span class="operator">*</span> quantityOrdered) <span class="keyword">as</span> total</span><br><span class="line">		<span class="keyword">from</span> </span><br><span class="line">			orders o</span><br><span class="line">				<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">			orderdetails o2 <span class="keyword">using</span>(orderNumber)</span><br><span class="line">		<span class="keyword">where</span></span><br><span class="line">			customerNumber <span class="operator">=</span> customers.customerNumber </span><br><span class="line">		<span class="keyword">group</span> <span class="keyword">by</span> </span><br><span class="line">			orderNumber </span><br><span class="line">		<span class="keyword">having</span> </span><br><span class="line">			<span class="built_in">sum</span>(priceEach <span class="operator">*</span> quantityOrdered) <span class="operator">&gt;</span> <span class="number">60000</span></span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<h2 id="派生表"><a href="#派生表" class="headerlink" title="派生表"></a>派生表</h2><p>派生表是从<code>SELECT</code>语句返回的虚拟表，当在SELECT语句的FROM子句中使用独立子查询时，将其称为派生表。</p>
<p><img src="image-20211012092439590.png" alt="image-20211012092439590"></p>
<blockquote>
<p>独立子查询时一个子查询，它可以独立于包含它的语句执行。</p>
</blockquote>
<p>派生表必须要有别名。</p>
<p>示例：</p>
<p>2013年顾客分为3组，platinum、glod和silver，需知道每组客户的数量，并满足以下条件：</p>
<ol>
<li>订单数量大于100k的白金客户</li>
<li>订单量在10k到100k之间的黄金客户</li>
<li>订单量小于10k的白银客户</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	cg.customerGroup,</span><br><span class="line">	<span class="built_in">count</span>(cg.customerGroup) <span class="keyword">as</span> groupCount</span><br><span class="line"><span class="keyword">from</span> (</span><br><span class="line">	<span class="keyword">select</span> </span><br><span class="line">	customerNumber,</span><br><span class="line">	round(<span class="built_in">sum</span>(quantityOrdered <span class="operator">*</span> priceEach)) sales,</span><br><span class="line">	(<span class="keyword">case</span></span><br><span class="line">		<span class="keyword">when</span> <span class="built_in">sum</span>(quantityOrdered <span class="operator">*</span> priceEach) <span class="operator">&lt;</span> <span class="number">10000</span> <span class="keyword">then</span> <span class="string">&#x27;silver&#x27;</span></span><br><span class="line">		<span class="keyword">when</span> <span class="built_in">sum</span>(quantityOrdered <span class="operator">*</span> priceEach) <span class="keyword">between</span> <span class="number">10000</span> <span class="keyword">and</span> <span class="number">100000</span> <span class="keyword">then</span> <span class="string">&#x27;gold&#x27;</span></span><br><span class="line">		<span class="keyword">when</span> <span class="built_in">sum</span>(quantityOrdered <span class="operator">*</span> priceEach) <span class="operator">&gt;</span> <span class="number">100000</span> <span class="keyword">then</span> <span class="string">&#x27;platinum&#x27;</span></span><br><span class="line">	<span class="keyword">end</span>) <span class="keyword">as</span> customerGroup</span><br><span class="line">	<span class="keyword">from</span> </span><br><span class="line">		orderdetails o</span><br><span class="line">			<span class="keyword">inner</span> <span class="keyword">join</span></span><br><span class="line">		orders <span class="keyword">using</span>(orderNumber)</span><br><span class="line">	<span class="keyword">where</span> </span><br><span class="line">		<span class="keyword">year</span>(shippedDate) <span class="operator">=</span> <span class="number">2013</span></span><br><span class="line">	<span class="keyword">group</span> <span class="keyword">by</span> customerNumber</span><br><span class="line">) <span class="keyword">as</span> cg</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> cg.customerGroup;</span><br></pre></td></tr></table></figure>


        <hr>
        <!-- Pager -->
        <ul class="pager">
          
          <li class="previous">
            <a href="/cn/java/Java NIO/" data-toggle="tooltip" data-placement="top" title="Java NIO">&larr; Previous Post</a>
          </li>
          
          
          <li class="next">
            <a href="/cn/mysql/MySQL窗口函数/" data-toggle="tooltip" data-placement="top" title="MySQL窗口函数">Next Post &rarr;</a>
          </li>
          
        </ul>

        
        <!-- tip start -->
        <!-- tip -->
<!-- tip start -->
<div class="tip">
  <p>
    
      如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。 也欢迎您共享此博客，以便更多人可以参与。 如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。 谢谢 ！
    
  </p>
</div>
<!-- tip end -->

        <!-- tip end -->
        

        
        <hr>

        <!-- comments start -->
        <!-- 1. gitalk comment -->


<!-- 2. gitment comment -->


<!-- 3. disqus comment -->


        <!-- comments end -->
        <hr>

      </div>

      <!-- Catalog: Tabe of Content -->
      <!-- Table of Contents -->



      <!-- Sidebar Container -->
      <div class="
                col-lg-8 col-lg-offset-1
                col-md-10 col-md-offset-1
                sidebar-container">

        <!-- Featured Tags -->
        
        <section>
          <!-- no hr -->
          <h5>
            <a href="/tags/">特色标签</a>
          </h5>
          <div class="tags">
            
            <a class="tag" href="/tags/#数据库" title="数据库">数据库</a>
            
            <a class="tag" href="/tags/#mysql" title="mysql">mysql</a>
            
            <a class="tag" href="/tags/#SQL" title="SQL">SQL</a>
            
          </div>
        </section>
        

        <!-- Friends Blog -->
        
        <hr>
        <h5>链友</h5>
        <ul class="list-inline">

          
        </ul>
        
      </div>
    </div>
  </div>
</article>



<!-- anchorjs start -->
<!-- async load function -->
<!-- anchor-js, Doc:http://bryanbraun.github.io/anchorjs/ -->
<script type="text/javascript">
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function(e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  };
</script>
<script type="text/javascript">
  //anchor-js, Doc:http://bryanbraun.github.io/anchorjs/
  async ("https://cdn.bootcss.com/anchor-js/1.1.1/anchor.min.js", function() {
    anchors.options = {
      visible: 'hover',
      placement: 'left',
      // icon: 'ℬ'
      icon: '❡'
    };
    anchors.add().remove('.intro-header h1').remove('.subheading').remove('.sidebar-container h5');
  });
</script>
<style>
  /* place left on bigger screen */
  @media all and (min-width: 800px) {
    .anchorjs-link {
      position: absolute;
      left: -0.75em;
      font-size: 1.1em;
      margin-top: -0.1em;
    }
  }
</style>

<!-- anchorjs end -->



		<!-- Footer (contains ThemeColor、viewer) -->
		<!-- Footer -->
<footer>
  <div class="container">
    <div class="row">
      <div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
        <ul class="list-inline text-center">
          

          

          

          

          

          

          

          

        </ul>
        <p class="copyright text-muted">
          Copyright &copy;
          zhuang
          2021
          <br>
          Theme by
          <a target="_blank" rel="noopener" href="http://beantech.org">BeanTech</a>
          <span style="display: inline-block; margin: 0 5px;">
            <i class="fa fa-heart"></i>
          </span>
          re-Ported by
          <a target="_blank" rel="noopener" href="https://v-vincen.life/">Live My Life</a>
        </p>
      </div>
    </div>
  </div>
</footer>

<a id="rocket" href="#top" class=""></a>


  <!-- jQuery -->
  <script type="text/javascript" src="/js/jquery.min.js"></script>
  <!-- Bootstrap Core JavaScript -->
  <script type="text/javascript" src="/js/bootstrap.min.js"></script>
  <!-- Custom Theme JavaScript -->
  <script type="text/javascript" src="/js/hux-blog.min.js"></script>
  <!-- catalog -->
  <script async="true" type="text/javascript" src="/js/catalog.js"></script>
  <!-- totop(rocket) -->
  <script async="true" type="text/javascript" src="/js/totop.js"></script>

  
    <!-- Busuanzi JavaScript -->
    <script async="async" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
  

  
    <!-- Scroll start -->
    <script async="async" type="text/javascript" src="/js/scroll.js"></script>
    <!-- Scroll end -->
  

  

  

  
    <!-- ribbon -->
    <script type="text/javascript" src="/js/ribbonDynamic.js"></script>
  

  






  <!-- viewer start -->
  <!-- viewer start (Picture preview) -->
  
    <script async="async" type="text/javascript" src="/js/viewer/viewer.min.js"></script>
    <script async="async" type="text/javascript" src="/js/viewer/pic-viewer.js"></script>
  

  <!-- viewer end -->


<script>
  // async load function
  function async (u, c) {
    var d = document,
      t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) {
      o.addEventListener('load', function (e) {
        c(null, e);
      }, false);
    }
    s.parentNode.insertBefore(o, s);
  }

  // fastClick.js
  async ("https://cdn.bootcss.com/fastclick/1.0.6/fastclick.min.js", function () {
    var $nav = document.querySelector("nav");
    if ($nav)
      FastClick.attach($nav);
    }
  )
</script>

<!-- Because of the native support for backtick-style fenced code blocks right within the Markdown is landed in Github Pages, From V1.6, There is no need for Highlight.js, so Huxblog drops it officially. -
https://github.com/blog/2100-github-pages-now-faster-and-simpler-with-jekyll-3-0 - https://help.github.com/articles/creating-and-highlighting-code-blocks/ -->
<!-- <script> async ("http://cdn.bootcss.com/highlight.js/8.6/highlight.min.js", function () { hljs.initHighlightingOnLoad(); }) </script> <link href="http://cdn.bootcss.com/highlight.js/8.6/styles/github.min.css" rel="stylesheet"> -->

<!-- jquery.tagcloud.js -->
<!-- <script> // only load tagcloud.js in tag.html if ($('#tag_cloud').length !== 0) { async ("http://yoursite-url/js/jquery.tagcloud.js", function () { $.fn.tagcloud.defaults = { // size: { start: 1, end: 1, unit: 'em' }, color: {
start: '#bbbbee', end: '#0085a1' } }; $('#tag_cloud a').tagcloud(); }) } </script> -->


		<!-- Search -->
		
		<div class="popup search-popup local-search-popup">
  <span class="popup-btn-close">
    ESC
  </span>
  <div class="container">
    <div class="row">
      <!-- <div class="col-md-9 col-md-offset-1"> -->
      <div class="col-lg-9 col-lg-offset-1 col-md-10 col-md-offset-1 local-search-content">

        <div class="local-search-header clearfix">

          <div class="local-search-input-wrapper">
            <span class="search-icon">
              <i class="fa fa-search fa-lg" style="margin: 25px 10px 25px 20px;"></i>
            </span>
            <input autocomplete="off" placeholder="搜索..." type="text" id="local-search-input">
          </div>
        </div>
        <div id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>


  
    <script src="/js/ziploader.js"></script>
  
  <script>
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.json";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    // monitor main search box;
    var onPopupClose = function (e) {
      $('.popup').fadeOut(300);
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $('.popup').fadeIn(300);
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }
    // get search zip version
    $.get('/searchVersion.json?t=' + (+new Date()), function (res) {
      if (localStorage.getItem('searchVersion') !== res) {
        localStorage.setItem('searchVersion', res);
        initSearchJson();
      }
    });

    function initSearchJson() {
      initLoad(['/search.flv'], {
        loadOptions: {
          success: function (obj) {
            localStorage.setItem('searchJson', obj['search.json'])
          },
          error: function (e) {
            return console.log(e)
          }
        },
        returnOptions: {
          'json': TYPE_TEXT
        },
        mimeOptions: {
          'json': 'application/json'
        }
      })
    }
    // search function;
    var searchFunc = function (search_id, content_id) {
      'use strict';
      isfetched = true;
      var datas = JSON.parse(localStorage.getItem('searchJson'));
      // console.log(search_id)
      var input = document.getElementById(search_id);
      var resultContent = document.getElementById(content_id);
      var inputEventFunction = function () {
        var searchText = input.value.trim().toLowerCase();
        var keywords = searchText.split(/[\s\-]+/);
        if (keywords.length > 1) {
          keywords.push(searchText);
        }
        var resultItems = [];
        if (searchText.length > 0) {
          // perform local searching
          datas.forEach(function (data) {
            var isMatch = false;
            var hitCount = 0;
            var searchTextCount = 0;
            var title = data.title
              ? data.title.trim()
              : '';
            var titleInLowerCase = title.toLowerCase();
            var content = data.content
              ? data.content.trim().replace(/<[^>]+>/g, "")
              : '';
            var contentInLowerCase = content.toLowerCase();
            var articleUrl = decodeURIComponent(data.url);

            var date = data.date;
            var dateTime = date.replace(/T/, " ").replace(/.000Z/, "");
            var imgUrl = data.header_img;
            


            var indexOfTitle = [];
            var indexOfContent = [];
            // only match articles with not empty titles
            keywords.forEach(function (keyword) {
              function getIndexByWord(word, text, caseSensitive) {
                var wordLen = word.length;
                if (wordLen === 0) {
                  return [];
                }
                var startPosition = 0,
                  position = [],
                  index = [];
                if (!caseSensitive) {
                  text = text.toLowerCase();
                  word = word.toLowerCase();
                }
                while ((position = text.indexOf(word, startPosition)) > -1) {
                  index.push({position: position, word: word});
                  startPosition = position + wordLen;
                }
                return index;
              }
              indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
              indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
            });
            if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
              isMatch = true;
              hitCount = indexOfTitle.length + indexOfContent.length;
            }
            // show search results
            if (isMatch) {
              // sort index by position of keyword
              [indexOfTitle, indexOfContent].forEach(function (index) {
                index.sort(function (itemLeft, itemRight) {
                  if (itemRight.position !== itemLeft.position) {
                    return itemRight.position - itemLeft.position;
                  } else {
                    return itemLeft.word.length - itemRight.word.length;
                  }
                });
              });
              // merge hits into slices
              function mergeIntoSlice(text, start, end, index) {
                var item = index[index.length - 1];
                var position = item.position;
                var word = item.word;
                var hits = [];
                var searchTextCountInSlice = 0;
                while (position + word.length <= end && index.length != 0) {
                  if (word === searchText) {
                    searchTextCountInSlice++;
                  }
                  hits.push({position: position, length: word.length});
                  var wordEnd = position + word.length;
                  // move to next position of hit
                  index.pop();
                  while (index.length != 0) {
                    item = index[index.length - 1];
                    position = item.position;
                    word = item.word;
                    if (wordEnd > position) {
                      index.pop();
                    } else {
                      break;
                    }
                  }
                }
                searchTextCount += searchTextCountInSlice;
                return {hits: hits, start: start, end: end, searchTextCount: searchTextCountInSlice};
              }
              var slicesOfTitle = [];
              if (indexOfTitle.length != 0) {
                slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
              }
              var slicesOfContent = [];
              while (indexOfContent.length != 0) {
                var item = indexOfContent[indexOfContent.length - 1];
                var position = item.position;
                var word = item.word;
                // cut out 100 characters
                var start = position - 20;
                var end = position + 80;
                if (start < 0) {
                  start = 0;
                }
                if (end < position + word.length) {
                  end = position + word.length;
                }
                if (end > content.length) {
                  end = content.length;
                }
                slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
              }
              // sort slices in content by search text's count and hits' count
              slicesOfContent.sort(function (sliceLeft, sliceRight) {
                if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                  return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                  return sliceRight.hits.length - sliceLeft.hits.length;
                } else {
                  return sliceLeft.start - sliceRight.start;
                }
              });
              // select top N slices in content
              var upperBound = parseInt('1');
              if (upperBound >= 0) {
                slicesOfContent = slicesOfContent.slice(0, upperBound);
              }
              // highlight title and content
              function highlightKeyword(text, slice) {
                var result = '';
                var prevEnd = slice.start;
                slice.hits.forEach(function (hit) {
                  result += text.substring(prevEnd, hit.position);
                  var end = hit.position + hit.length;
                  result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                  prevEnd = end;
                });
                result += text.substring(prevEnd, slice.end);
                return result;
              }
              var resultItem = '';

              // if (slicesOfTitle.length != 0) {   resultItem += "<li><a target='_blank' href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>"; } else {   resultItem += "<li><a target='_blank' href='" +
              // articleUrl + "' class='search-result-title'>" + title + "</a>"; } slicesOfContent.forEach(function (slice) {   resultItem += "<a target='_blank' href='" + articleUrl + "'><p class=\"search-result\">" + highlightKeyword(content, slice) +
              // "...</p></a>"; }); resultItem += "</li>";

              if (slicesOfTitle.length != 0) {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</div><time class='search-result-date'>" + dateTime + "</time>";
              } else {
                resultItem += "<a target='_blank' href='" + articleUrl + "' class='search-result'><div class='search-result-left'><div class='search-result-title'>" + title + "</div><time class='search-result-date'>" + dateTime + "</time>";
              }
              slicesOfContent.forEach(function (slice) {
                resultItem += "<p class=\"search-result-content\">" + highlightKeyword(content, slice) + "...</p>";
              });
              resultItem += "</div><div class='search-result-right'><img class='media-image' src='" + imgUrl + "' width='64px' height='48px'></img></div></a>";

              resultItems.push({item: resultItem, searchTextCount: searchTextCount, hitCount: hitCount, id: resultItems.length});
            }
          })
        };

        if (keywords.length === 1 && keywords[0] === "") {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else if (resultItems.length === 0) {
          resultContent.innerHTML = '<div id="no-result"></div>'
        } else {
          resultItems.sort(function (resultLeft, resultRight) {
            if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
              return resultRight.searchTextCount - resultLeft.searchTextCount;
            } else if (resultLeft.hitCount !== resultRight.hitCount) {
              return resultRight.hitCount - resultLeft.hitCount;
            } else {
              return resultRight.id - resultLeft.id;
            }
          });
          var searchResultList = '<div class=\"search-result-list\">';
          resultItems.forEach(function (result) {
            searchResultList += result.item;
          })
          searchResultList += "</div>";
          resultContent.innerHTML = searchResultList;
        }
      }
      if ('auto' === 'auto') {
        input.addEventListener('input', inputEventFunction);
      } else {
        $('.search-icon').click(inputEventFunction);
        input.addEventListener('keypress', function (event) {
          if (event.keyCode === 13) {
            inputEventFunction();
          }
        });
      }
      // remove loading animation
      $('body').css('overflow', '');
      proceedsearch();
    }
    // handle and trigger popup window;
    $('.popup-trigger').click(function (e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc('local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });
    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function (e) {
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 && $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });

    document.addEventListener('mouseup', (e) => {
      var _con = document.querySelector(".local-search-content");
      if (_con) {
        if (!_con.contains(e.target)) {
          onPopupClose();
        }
      }
    });
  </script>


		
	<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
</body>
</html>
